import pymysql
import Simulation
import frozen_dir


class database(Simulation.Simulation_cfg):

    def Create_conn(self):
        # conn = pymysql.connect('localhost', user="root", passwd="lihongchen0531")
        # conn = pymysql.connect('rm-m5ei41q6w870w5413ho.mysql.rds.aliyuncs.com', user="lhc_0531",
        #                        passwd="@lihongchen0531")
        conn = pymysql.connect(self.Flag_db_port, user=self.Flag_db_user,
                               passwd=self.Flag_db_password)
        return conn

    def Connect_table(self):
        # conn = pymysql.connect('localhost', user="root", passwd="lihongchen0531", db="Bus_priority")
        conn = pymysql.connect(self.Flag_db_port, user=self.Flag_db_user,
                               passwd=self.Flag_db_password, db="Bus_priority")
        return conn

    def Create_DB(self):
        conn = self.Create_conn()
        cursor = conn.cursor()
        # 创建pythonBD数据库
        cursor.execute('CREATE DATABASE IF NOT EXISTS Bus_priority DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        # 创建路口表
        # 路口编号、相位、道路组成、初始时间
        sql = """
        create table if not exists Crossing(
            Crossing_id char (4) primary key,
            E_roadid char (64),
            W_roadid char (64),
            S_roadid char (64),
            N_roadid char (64),
            EW_straight int (8),
            EW_left int (8),
            NS_straight int (8),
            NS_left int (8),
            Initial_time int (8),
            Crossing_x char (64),
            Crossing_y char (64)
        );"""
        cursor.execute(sql)

        # 创建道路表
        # 道路编号、道路名称、流量、左右直行流量
        sql = """
        create table if not exists Road(
            Road_id char (16) primary key,
            Road_name char (16),
            Total_flow int (8),
            L_flow int (8),
            R_flow int (8),
            S_flow int (8)
        );"""
        cursor.execute(sql)

        # 创建公交车表
        # 公交车编号、公交车位置、乘客数量、延误时间、速度、x坐标、y坐标
        sql = """
            create table if not exists Bus(
                Bus_id char (16) primary key ,
                Road_id char (16),
                Passager_num int (8),
                Delay_time int (8),
                Speed char (16),
                x_coordinate char (64),
                y_coordinate char (64),
                foreign key (Road_id) references Road(Road_id)
            );"""
        cursor.execute(sql)

        # 创建请求表
        # 请求路口编号、请求公交车编号、请求时间、请求相位、处理策略
        # sql = """
        # create table if not exists Request(
        #     Crossing_id char (4),
        #     Bus_id char (4),
        #     RTime Datetime,
        #     Strategy char(20),
        #     Request_stage char (20),
        #     primary key (Crossing_id, Bus_id),
        #     foreign key (Crossing_id) references Crossing(Crossing_id),
        #     foreign key (Bus_id) references Bus(Bus_id)
        # );"""

        sql = """
        create table if not exists Request(
            Crossing_id char (20),
            Bus_id char (20),
            RTime char (20),
            Strategy char(20),
            Request_stage char (20),
            Delta_PI char (20)
        );"""
        cursor.execute(sql)

        # 创建公交车站表
        # 车站编号、车站名称、车站xy坐标
        sql = """
        create table if not exists Station(
            Station_id char (4),
            Station_name char (64),
            Station_x char (64),
            Station_y char (64),
            primary key (Station_id)
        );"""
        cursor.execute(sql)

        # 创建所有公交车历史信息表格
        # 公交车编号、仿真步次、公交车所在路名称、公交车速度、乘客数量、公交车x、y坐标
        sql = """
            create table if not exists His_Bus(
                His_Bus_id char (4),
                His_Bus_step char (64),
                His_Bus_road char (64),
                His_Bus_speed char (64),
                His_Bus_passager char (64),
                His_Bus_x char (64),
                His_Bus_y char (64),
                His_Bus_CO char (64),
                His_Bus_CO2 char (64),
                His_Bus_HC char (64),
                His_Bus_NOX char (64)
            );"""
        cursor.execute(sql)

        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return 0

    def Init_DB(self):
        """
        初始化数据库，清除所有数据
        :return:
        """
        try:
            conn = self.Create_conn()
            cursor = conn.cursor()  # 获取游标
            sql = 'drop database if exists Bus_priority '
            cursor.execute(sql)
            conn.commit()  # 提交
            cursor.close()  # 关闭游标
            conn.close()  # 退出连接
        except:
            self.Create_DB()
        self.Create_DB()

    def add_Crossing(self, args):
        """
        添加路口信息
        :param args: 按照顺序分别是：id、东西南北id、信号相位、初始时间、xy坐标
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into bus_priority.Crossing value (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def add_Road(self, args):
        """
        添加道路信息
        :param args: 按照顺序分别是：道路编号、道路名称、流量、左右直行流量
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into bus_priority.Road value (%s, %s, %s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def add_Bus(self, args):
        """
        添公交车信息
        :param args: 按照顺序分别是：公交车编号、公交车位置、乘客数量、延误时间、x坐标、y坐标
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into bus_priority.Bus value (%s, %s, %s, %s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def add_Request(self, args):
        """
        添加请求信息
        :param args: 按照顺序分别是：请求路口编号、请求公交车编号、请求时间、请求相位、处理策略
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into bus_priority.Request value (%s, %s, %s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def add_Station(self, args):
        """
        添加站点信息
        :param args:
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into bus_priority.Station value (%s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def add_His_Bus(self, args):
        """
        添加站点信息
        :param args:
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into bus_priority.His_Bus value (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def OC_Filling(self):
        """
        一键填充数据库，数据来源'data/data.xlsx'
        :return:
        """
        import xlrd
        from datetime import datetime
        from xlrd import xldate_as_tuple
        dir = frozen_dir.app_path() + r'\data\data.xlsx'
        workBook = xlrd.open_workbook(dir)
        sheet_name = ['road', 'bus', 'crossing', 'station', 'request']
        for item in sheet_name:
            sheet = workBook.sheet_by_name(item)
            print(sheet)
            for i in range(1, sheet.nrows):
                mess = (tuple(sheet.row_values(i)))
                if item == 'road':
                    self.add_Road(mess)
                elif item == 'bus':
                    self.add_Bus(mess)
                elif item == 'crossing':
                    self.add_Crossing(mess)
                elif item == 'station':
                    self.add_Station(mess)
                elif item == 'request':
                    self.add_Request(mess)

        # sheet = workBook.sheet_by_name('request')
        # # 由于request中RTime为时间类型，需要单独进行转换，参考：
        # # https://www.cnblogs.com/xxiong1031/archive/2004/01/13/7069006.html
        # for i in range(1, sheet.nrows):
        #     mess = sheet.row_values(i)
        #     cell = sheet.cell_value(i, 2)
        #     date = datetime(*xldate_as_tuple(cell, 0))
        #     cell = date.strftime('%Y/%m/%d %H:%M:%S')
        #     mess[2] = cell
        #     mess = tuple(mess)
        #     try:
        #         self.add_Request(mess)
        #     except:
        #         print('插入失败，可能未满足外键约束')

    def Fill_With_File(self, filename):
        """
        一键填充数据库，数据来源'data/data.xlsx'
        :return:
        """
        import xlrd
        from datetime import datetime
        from xlrd import xldate_as_tuple
        workBook = xlrd.open_workbook(filename)
        sheet_name = ['road', 'bus', 'crossing', 'station', 'request']
        for item in sheet_name:
            sheet = workBook.sheet_by_name(item)
            print(sheet)
            for i in range(1, sheet.nrows):
                mess = (tuple(sheet.row_values(i)))
                if item == 'road':
                    self.add_Road(mess)
                elif item == 'bus':
                    self.add_Bus(mess)
                elif item == 'crossing':
                    self.add_Crossing(mess)
                elif item == 'station':
                    self.add_Station(mess)
                elif item == 'request':
                    self.add_Request(mess)

    def Read_Bus(self):
        """
        读取公交车信息
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT * FROM bus_priority.bus;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return results

    def Read_Request(self):
        """
        读取公交车信息
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT * FROM bus_priority.request;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return results

    def Read_Road(self):
        """
        读取道路信息
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT * FROM bus_priority.road;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return results

    def Read_Crossing(self):
        """
        读取交叉口信息
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT * FROM bus_priority.crossing;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return results

    def Read_Station(self):
        """
        读取交叉口信息
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT * FROM bus_priority.station;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return results

    def Read_His_Bus(self):
        """
        读取历史公交车信息
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT * FROM bus_priority.his_bus;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return results

    def Update_Road(self, id):
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "update bus_priority.crossing" \
              "set Passager_num 10" \
              "where Bus_id = 1"
        cursor.execute(sql)
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return 0

    def Update_Bus(self, bus_id, Road_id=None, Passager_num=None, Delay_time=None, coo_x=None, coo_y=None):
        """this function has some bugs, so I strongly recommend that you do not use it"""
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        # sql = "update bus_priority.bus set Passager_num 10 where Bus_id = 1"
        sql = "update bus_priority.bus set "
        if Road_id is not None:
            sql += "Road_id=" + "'" + str(Road_id) + "'" + " "
        if Passager_num is not None:
            sql += "Passager_num=" + str(Passager_num) + " "
        if Delay_time is not None:
            sql += "Delay_time=" + str(Delay_time) + " "
        if coo_x is not None:
            sql += "x_coordinate=" + "'" + str(coo_x) + "'" + " "
        if coo_y is not None:
            sql += "y_coordinate=" + "'" + str(coo_y) + "'" + " "
        sql += "where Bus_id=" + "'" + str(bus_id) + "'"

        print(sql)
        cursor.execute(sql)
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        return 0

    def Delete_Bus(self):
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "delete from bus_priority.bus"
        cursor.execute(sql)
        conn.commit()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def Delete_His_Bus(self):
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "delete from bus_priority.his_bus"
        cursor.execute(sql)
        conn.commit()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接

    def Get_Col_Name(self, table_name):
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "select column_name from information_schema.columns " \
              "where table_name="
        sql = sql + "'" + table_name + "'" + ' ORDER BY ordinal_position;'
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        col_name = []
        for item in results:
            col_name.append(str(item[0]))
        return col_name

    def GET_all_Road_id(self):
        """
        获取道路主键：id
        :return: list, 升序排列，内元素str
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT Road_id FROM bus_priority.road;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        ans_list = []
        for item in results:
            ans_list.append(int(item[0]))
        ans_list.sort()
        return [str(x) for x in ans_list]

    def GET_all_Road_name(self):
        """
        获取道路主键：id
        :return: list, 升序排列，内元素str
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT Road_name FROM bus_priority.road;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        ans_list = []
        for item in results:
            ans_list.append(str(item[0]))
        ans_list.sort()
        return [str(x) for x in ans_list]

    def GET_all_Bus_id(self):
        """
        获取公交车主键：id
        :return: list, 升序排列，内元素str
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT Bus_id FROM bus_priority.bus;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接
        ans_list = []
        for item in results:
            ans_list.append(int(item[0]))
        ans_list = list(set(ans_list))  # 去重，按理说id作为主键，不会重复
        ans_list.sort()
        return [str(x) for x in ans_list]

    def GET_all_Crossing_id(self):
        """
        获取路口主键：id
        :return:list, 升序排列，内元素str
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = "SELECT Crossing_id FROM bus_priority.crossing;"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()
        conn.close()
        ans_list = []
        for item in results:
            ans_list.append(int(item[0]))
        ans_list = list(set(ans_list))  # 去重，按理说id作为主键，不会重复
        ans_list.sort()
        return [str(x) for x in ans_list]


if __name__ == '__main__':
    db = database()
    db.Init_DB()
    db.OC_Filling()
    # print(db.Read_His_Bus())

